A join is a query that combines rows from two or more sources, such as tables, views, or snapshots. In the context of database systems, a join is performed whenever multiple tables appear in a query's FROM clause. The query's select list can select any columns from any of the base tables listed in the FROM clause.
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. Query1 is an equijoin that combines the rows of tables R and S where the value in column r.b is the same as the value in column s.b:
QUERY1SELECT *FROM R, SWHERE r.b = s.b;
For the purpose of illustration, assume that tables R and S contain the following rows:
TABLE RaBWAXAYBZC
TABLE SaB1A2C3C4D
Under these circumstances, Query 1 would produce the result set:
R.aR.bS.aS.bWA1AXA1AZC2CZC3C
In this example, two rows ((W, A) and (X, A)) in table R combine with row (1, A) in row S. Therefore, row (1, A) appears twice in the result set. Similarly, row (Z, C) in table R combines with two rows ((2, C) and (3, C)) in table S. Therefore, row (Z, C) appears twice in the result set. Row (Y, B) of table R does not combine with any row in table S, so row (Y, B) is not reflected in the result set of the equijoin. Similarly, row (4, D) of table S did not combine with any row in table R, so row (4, D) is not reflected in the result set of the equijoin.
An equijoin is an example of a binary operation that produces a result multi-set (a multi-set is a collection of items that allows non-distinct items) based on the contents of two multi-set sources. Other binary SQL operations that produce result set based on two multi-set sources are minus and intersect. Each of these operations shall be described in greater detail hereafter.